# -*- coding: utf-8 -*-
import datetime

import pymysql
from scrapy import log

log.msg("This is a warning", level=log.DEBUG)


# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html


class HospitalinfoPipeline(object):

    def process_item(self, item, spider):
        # config = {'host': '172.16.49.89', 'user': 'view', 'password': 'Ki2Tv@m!&s@mPj9VLS5D', 'db': 'spider_db',
        #           'charset': 'utf8mb4'}
        # config = {'host': '127.0.0.1', 'user': 'root', 'password': '123456', 'db': 'hospital',
        #           'charset': 'utf8mb4'}
        config = {'host': 'rm-bp1r11ynaceh2h2p5-rm-bp1r11ynaceh2h2p5788.mysql.rds.aliyuncs.com', 'user': 'root', 'password': 'LEMON521$', 'db': 'hospital',
                  'charset': 'utf8mb4'}
        connection = pymysql.connect(**config)
        name = item["name"]
        already_exist = 0
        property = item["property"]
        grade = item["grade"]
        tel = str(item["tel"])
        province = item["province"]
        city = item["city"]
        district = item["district"]
        address = item["address"]
        website = item["website"]
        email = ''
        dean = item["dean"]
        is_health_insurance = str(1) if item["is_health_insurance"] == '医保' else str(0)
        summary = item["summary"]
        bus_route = item["bus_route"]
        advanced_equipment = item["advanced_equipment"]
        honor = item["honor"]
        created_time = str(item["created_time"])
        gmt_created = str(datetime.datetime.now())
        deleted = str(0)
        sqlad = "select * from yd_hospital where name ='" + name + "'"
        try:
            already_exist = connection.query(sqlad)
        except Exception as err:
            print(err)
            log.msg(err, level=log.DEBUG)
        print('result' + str(already_exist))
        if already_exist == 0:
            print(
                "=============================================================================================================")
            sql = "insert into yd_hospital(name,province,city,district,property,grade,tel,address,website,email,dean,is_health_insurance,summary,bus_route,advanced_equipment,honor,created_time, gmt_created,deleted)" \
                  " VALUES('" + name + "','" + province + "','" + city + "','"+ district+"','"+ property + "','" + grade + "','" + tel + "','" + address + "','" + website + "','" + email + "','" + dean + "','" + is_health_insurance + "','" + summary + "','" + bus_route + "'," \
                                                                                                                                                                                                                               "'" + advanced_equipment + "','" + honor + "','" + created_time + "','" + gmt_created + "','" + deleted + "')"
            # print(sql)
            # 异常处理
            try:
                connection.query(sql)
                log.msg(sql, level=log.DEBUG)
                connection.commit()
            except Exception as err:
                print(err)
                log.msg(err, level=log.DEBUG)
        connection.close()
